package my.framework.dao;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 执行数据库操作的工具类
 */
public class CommonDao {
    /**
     * 增，删，改操作
     * @param sql
     * sql语句
     * @param params
     * 参数数组
     * @return 执行结果
     * @throw SQLException
     */
    public int executeUpdate(String sql,Object[] params)throws SQLException {
        int result = 0;
        PreparedStatement pstmt = null;
        try {
            pstmt = DatabaseUtil.getConnection().prepareStatement(sql);
            if (!(params == null || params.length == 0)) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeStatement(pstmt, null);
        }
        return result;
    }
    public int executeUpdate(String sql,Object entity)throws SQLException{
        Map<String,Object[]> mappingResult=hanleParameterMapping(sql,entity);
        Map.Entry<String,Object[]> entry=mappingResult.entrySet().iterator().next();
        return executeUpdate(entry.getKey(),entry.getValue());
    }
    /**
     * 解析带占位符的SQL语句，处理Java对象的属性到SQL参数的映射
     */
    protected Map<String,Object[]> hanleParameterMapping(String sql,Object entity) {
        Map<String, Object[]> mappingResult = new HashMap<String, Object[]>();
        List<String> placeholders = new ArrayList<String>();
        int offset = 0;
        while (true) {  //查找SQL语句中的所有占位符，提取对应的变量名
            int start = sql.indexOf("#{", offset);
            if (start >= offset) {
                int end = sql.indexOf("}", start);
                placeholders.add(sql.substring(start + 2, end));
                offset = end + 1;
            } else {
                break;
            }
        }
        if (placeholders.size() > 0) {
            Object[] params = new Object[placeholders.size()];
            for (int i = 0; i < placeholders.size(); ++i) {
                String placeholder = placeholders.get(i);
                //将占位符换成？，#，{,}在正则表达式中是特殊字符，需要\转义
                sql = sql.replaceFirst("\\#\\{" + placeholder + "\\}", "?");
                try {//获取占位符对应的getter方法
                    Method getter = entity.getClass().getMethod("get" + placeholder.substring(0, 1).toUpperCase() + placeholder.substring(1));
                    //执行getter方法得到条件数据
                    Object param = getter.invoke(entity);
                    params[i] = param;
                } catch (NullPointerException | NoSuchMethodException
                        | SecurityException | IllegalAccessException
                        | IllegalArgumentException | InvocationTargetException e) {
                    throw new RuntimeException("无法为占位符#{" + placeholder + "}赋值！", e);
                }
            }
            mappingResult.put(sql, params); //返回解析后的SQL及条件数组
        }else{
            mappingResult.put(sql,new Object[]{});
        }
        return mappingResult;
    }

    /**
     * 查询操作
     * @param sql
     * sql语句
     * @param params
     * 参数数组
     * @return 查询结果
     * @throws SQLException
     */
    public <T> List<T> executeQuery(Class<T> clz,String sql,Object[] params)throws  SQLException{
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try{
            pstmt=DatabaseUtil.getConnection().prepareStatement(sql);
            if(!(params==null||params.length==0)){
                for (int i=0;i<params.length;i++){
                    pstmt.setObject(i+1,params[i]);
                }
            }
            rs= pstmt.executeQuery();
            return handleResultMapping(clz,rs);
        }catch (SQLException e){
            e.printStackTrace();
            throw e;
        }catch( InstantiationException |IllegalAccessException|IllegalArgumentException|InvocationTargetException e){
            throw new RuntimeException("封装查询结果时出现错误！",e);
        }finally {
            DatabaseUtil.closeStatement(pstmt,rs);
        }
    }
    public <T> List<T> executeQuery(Class<T> clz,String sql,Object entity)throws SQLException{
        Map<String,Object[]> mappingResult=hanleParameterMapping(sql, entity);
        Map.Entry<String,Object[]> entry=mappingResult.entrySet().iterator().next();
        return executeQuery(clz, entry.getKey(), entry.getValue());
    }

    /**
     * 解析查询结果集，将查询结果封装Java对象
     */
    @SuppressWarnings("unchecked")
    protected <T> List<T> handleResultMapping(Class<T> clz,ResultSet rs)throws SQLException,InstantiationException,IllegalAccessException,IllegalArgumentException,InvocationTargetException{
        if(clz.equals(Integer.class)){//结果是一个数字，适用于select count(*) from ..
            List<Integer> result=new ArrayList<Integer>();
            while(rs.next()){
                result.add(rs.getInt(1));
            }
            return (List<T>) result;
        }
        ResultSetMetaData metaData=rs.getMetaData(); //获得ResultSet的原数据
        int count=metaData.getColumnCount();  //获得结果集的列数
        //存储每一列对应的Java对象的setter
        Method[] setters=new Method[count];
        Class<?>[] types=new Class[count]; //存储setter方法的参数类型
        for (int i=0,j=1;i<count;++i,++j){
            String lable= metaData.getColumnLabel(j);//获取查询结果的列标题
            String type= metaData.getColumnTypeName(j);//获取该列数据库类型名
            try{
                setters[i]=clz.getMethod("set"+lable.substring(0,1).toUpperCase()+lable.substring(1),TypeConstant.getJavaType(type));
                types[i]=TypeConstant.getJavaType(type);
            }catch (NoSuchMethodException|SecurityException e){
                e.printStackTrace(); //log
                setters[i]=null;
                types[i]=null;
            }
        }
        List<T> result=new ArrayList<T>();
        T instance=null;
        while(rs.next()){ //从结果集取出每一列的值，调用Java对象中对应的setter赋值
            instance=clz.newInstance();
            for (int k=0;k<count;++k){
                if (setters[k]==null){
                    continue;
                }else {
                    //日期类型需要特殊处理
                    if(types[k].equals(java.util.Date.class)){
                        setters[k].invoke(instance,new java.util.Date(rs.getTimestamp(k+1).getTime()));
                    }else {
                        setters[k].invoke(instance,rs.getObject(k+1,types[k]));
                    }
                }
            }
            result.add(instance);
        }
        return  result;
    }
}
